Doobie Introduction

7 minute read

Database is always a thing for developer to consider, there are lots of tools in other languages. In this blog, Let’s introduce a tool for Scala developer: doobie

What is doobie?

doobie is a pure-functional JDBC layer for Scala.

doobie provides low-level access to everything in java.sql (as of Java 8), allowing you to write any JDBC program in a pure functional style.

We can say doobie is just a FP wrapper of JDBC, it just help you translate the FP style code to java.sql code.

How to install?

Add the following configuration into your build.sbt

scalacOptions += "-Ypartial-unification" // 2.11.9+

libraryDependencies ++= Seq(

  // Start with this one
  "org.tpolecat" %% "doobie-core"      % "0.8.8",

  // And add any of these as needed
  "org.tpolecat" %% "doobie-h2"        % "0.8.8",          // H2 driver 1.4.200 + type mappings.
  "org.tpolecat" %% "doobie-postgres"  % "0.8.8",          // Postgres driver 42.2.9 + type mappings.
  "org.tpolecat" %% "doobie-hikari"    % "0.8.8",          // HikariCP transactor.
  "org.tpolecat" %% "doobie-quill"     % "0.8.8",          // Support for Quill 3.4.10
  "org.tpolecat" %% "doobie-specs2"    % "0.8.8" % "test", // Specs2 support for typechecking statements.
  "org.tpolecat" %% "doobie-scalatest" % "0.8.8" % "test"  // ScalaTest support for typechecking statements.
)

doobie-core is the essential dependency.

doobie-h2 and doobie-postgres are the JDBC driver, please choose them as needed

doobie-hikari supply another implementation of Transactor which support to manage the connection pool.

From version 0.7, doobie-quill can help you generate sql from your model which is more safe and easy to maintain, but you can still compose sql by Fragment.

doobie-specs2 and doobie-scalatest are for test, choose them according to your test framework.

Core Concept

In simple terms doobie translate all the models in java.sql to corresponding Free Monad, and use these Free Monad to compose program, then interpret the program to real java.sql as needed.

ConnectionIO

The Free Monad of java.sql.Connection, the program of doobie will become ConnectionIO finally.

All the ConnectionIO chained by map or flatMap will be run in the same transaction.

type ConnectionIO[A] = Free[ConnectionOp, A]

trait ConnectionOp[A]

final case object Commit extends ConnectionOp[Unit]
final case object CreateStatement extends ConnectionOp[Statement]
final case class  PrepareStatement(a: String) extends ConnectionOp[PreparedStatement]
.....

Fragment

In Java, we use the lieral string to define sql statement and use ? as the placeholder of parameter which can be supplied laterly. for example

PreparedStatement updateAge = null;

String updateString = "update person set age = ? where name = ?";

updateAge = con.prepareStatement(updateString);

updateAge.setInt(1, 18);
updateAge.setString(2, "Tom");

updateAge.executeUpdate();

To get a completed PreparedStatement, we need to prepare 3 things

The sql template
The parameter posiontion
The parameter type

We don’t know if the sql is correct before runing it in Java.

To make it easier, doobie defined Fragment which can apply type level checking when we prepare the sql and remember the parameter position and type. for example

val age: Int =18
val name: String = "Tom"
val sql = sql"update person set age = $age where name = $name"
sql.update.run.unsafeRunSync

Here the sql interpolator will help us construct Fragment.

Query and Update

In Java, we run query or update by invoking different methods of PreparedStatement, for example

PreparedStatement selectPerson = connection.prepareStatement("select name from person");

ResultSet rs = selectPerson.executeQuery()

while (rs.next()) {
    String name = rs.getString("name");
    System.out.println("name: " + name);
}

PreparedStatement updateAge = connection.prepareStatement("update person set age = 18");
selectPerson.executeUpdate()

We can see the process of query and update are different, so doobie define two differnt models for them: Query and Update

Query

In Query we can control the expected type of query result, which can even let us apply more flexible checking on the number of result.

Query.unique

Return exactly one record, will raise error when there is no or more than 1 records in ResultSet

def getUnique[A: Read]: ResultSetIO[A] =
  (getNext[A], next).tupled.flatMap {
    case (Some(a), false) => FRS.delay(a)
    case (Some(_), true)  => FRS.raiseError(UnexpectedContinuation)
    case (None, _)        => FRS.raiseError(UnexpectedEnd)
  }

Query.option

Return 0(None) or 1(Some) record, will raise error when there are more than 1 records in ResultSet

def getOption[A: Read]: ResultSetIO[Option[A]] =
  (getNext[A], next).tupled.flatMap {
    case (a @ Some(_), false) => FRS.delay(a)
    case (Some(_), true)      => FRS.raiseError(UnexpectedContinuation)
    case (None, _)            => FRS.delay(None)
  }

Query.to[F[_]]

Put all the records in ResultSet into F[_], such as List

Query.nel

Put all the records in ResultSet into NonEmptyList, will raise error when there is no record.

def nel[A: Read]: ResultSetIO[NonEmptyList[A]] =
  (getNext[A], list).tupled.flatMap {
    case (Some(a), as) => FRS.delay(NonEmptyList(a, as))
    case (None, _)     => FRS.raiseError(UnexpectedEnd)
  }

Query.stream

Put all the records in ResultSet into fs2.Stream

Update

Update is simpler than Query, we just need to invoke Update.run to execute the update

Data Mapping

You may be already aware of this part, no matter what we do by Query and Update, we always need to consider

How to convert the Scala data type to Database data type
How to convert the Database data type to Scala data type

And for high level program, we also need to consider

How to convert the Scala data model to Database record
How to convert the Database record to Scala data model

doobie define 4 type class to help us do this work

Get[A] - convert Database data type to Scala data type
Put[A] - convert Scala data type to Database data type
Read[A] - convert Database record to Scala data model
Write[A] - convert Scala data model to Database record

Usually we don’t need to care about them, doobie already defined them for most of the data type, even for case class. we can query case class directly like this

case class Person(name:String, age:Int)

sql"select name, age from person".query[Person].to[List]

If the existing data mapping can not meet our requirements, we can always generate new data mapping from existing one.

Transactor

A Transactor is just the interpretor of Free Monad, which will translate the program to java.sql program.

We can set up the JDBC driver, connection pool in Transactor.

Usage

How to connect to a database?

To connect to a database, we need to add the corresponding database dependency in our configuration. And pass the JDBC driver name to Transactor.

implicit val cs:ContextShift = ???
val xa = Transactor.fromDriverManager[IO](
  "org.postgresql.Driver",     // driver classname
  "jdbc:postgresql:world",     // connect URL (driver-specific)
  "postgres",                  // user
  ""                          // password
)

How to run sql?

Use sql interpolator to construct a Fragment
Generate a Query by Fragment.query or Update by Fragment.update
Generate ConnectionIO by Query.to, Query.unique, Query.option or Update.run
Pass ConnectionIO to Transactor to geneate IO
Run the IO

val program: ConnectionIO[Int] = sql"select 42".query[Int].unique
val io:IO[Int] = program2.transact(xa)
io.unsafeRunSync

How to run a query?

Single Column

sql"select name from person"
.query[String]    // Query0[String]
.to[List]         // ConnectionIO[List[String]]
.transact(xa)     // IO[List[String]]
.unsafeRunSync    // List[String]

Multiple Column

sql"select name, age from person"
  .query[(String, Int)] //Query0[(String, Int)]
  .to[List]             //ConnectionIO[List[(String, Int)]]
  .transact(xa)         //IO[List[(String, Int)]]
  .unsafeRunSync        //List[(String, Int)]

Custom Model

case class Person(name:String, age:Int)
sql"select name, age from person"
  .query[Person]        //Query0[Person]
  .to[List]             //ConnectionIO[List[Person]]
  .transact(xa)         //IO[List[Person]]
  .unsafeRunSync        //List[Person]

With Condition

case class Person(name:String, age:Int)
val ageThreshold:Int = 18
sql"select name, age from person where age > ${ageThreshold}"
  .query[Person]        //Query0[Person]
  .to[List]             //ConnectionIO[List[Person]]
  .transact(xa)         //IO[List[Person]]
  .unsafeRunSync        //List[Person]

case class Person(name:String, age:Int)
val requiredName:NonEmptyList[String] = NonEmptyList.of("Tom", "Jerry", "John") 
sql"select name, age from person where " ++ Fragments.in(fr"name", requriedName)
  .query[Person]        //Query0[Person]
  .to[List]             //ConnectionIO[List[Person]]
  .transact(xa)         //IO[List[Person]]
  .unsafeRunSync        //List[Person]

How to insert a record?

  val person:Person = ???
  sql"insert into person (name, age) values ($person.name, $person.age)"
    .update               //Update0
    .run                  //ConnectionIO[Int]
    .transact(xa)         //IO[Int]
    .unsafeRunSync        //Int

How to update a record?

  sql"update person set age = 18 where name = 'Tom'"
    .update               //Update0
    .run                  //ConnectionIO[Int]
    .transact(xa)         //IO[Int]
    .unsafeRunSync        //Int

How to delete a record?

  sql"delete from person where name = 'Tom'"
    .update               //Update0
    .run                  //ConnectionIO[Int]
    .transact(xa)         //IO[Int]
    .unsafeRunSync        //Int

How to create new type mapping between Database and Scala?

Say we defined a Status to check if a person is free

  sealed trait Status
  case object Busy extends Status
  case object Free extends Status

We know there is no corresponding data type in Database. but we still want to compose program like this

  val status:Status = Free
  sql"update person set status = $status where name = 'Tom'"
    .update
    .run
    .transact(xa)
    .unsafeRunSync

  sql"select status from person where name 'Tome'"
    .query[Status]
    .unique
    .transact(xa)
    .unsafeRunSync

So we need to define the instance of Get[Status] and Put[Status] to tell doobie how to mapping Status

  object Status {
    implicit statusGet:Get[Status] = Get[String].map[Status](x => if(x == "busy") Busy else Free)
    implicit statusPut:Put[Status] = Put[String].contramap[Status](x => if(x == Busy) "busy" else "free")
  }

How to manage the connections?

doobie-hikari library supply another implementation of Transactor which support connection pool

val transactor: Resource[IO, HikariTransactor[IO]] =
  for {
    ce <- ExecutionContexts.fixedThreadPool[IO](32) // our connect EC
    be <- Blocker[IO]    // our blocking EC
    xa <- HikariTransactor.newHikariTransactor[IO](
            "org.h2.Driver",                        // driver classname
            "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1",   // connect URL
            "sa",                                   // username
            "",                                     // password
            ce,                                     // await connection here
            be                                      // execute JDBC operations here
          )
  } yield xa

How to do test?

doobile supply test library both for specs2 and scalatest, but it need to connect the database.

Specs2

class AnalysisTestSpec extends Specification with doobie.specs2.IOChecker {

  val transactor = Transactor.fromDriverManager[IO](
    "org.postgresql.Driver", "jdbc:postgresql:world", "postgres", ""
  )

  check(sql"select * from person".query[Person])
}

ScalaTest

class AnalysisTestScalaCheck extends FunSuite with Matchers with doobie.scalatest.IOChecker {

  val transactor = Transactor.fromDriverManager[IO](
    "org.postgresql.Driver", "jdbc:postgresql:world", "postgres", ""
  )

  test("query") { check(sql"select * from person".query[Person])}
}

Summary

Hope this document can help you, doobie has a very good official document, you can find more advanced usage there.

If you are interested in the source code of doobie, please pay attention to the Embedded model, it use a special solution to compose program with multile Free Monad.

Twitter Facebook LinkedIn